package com.xvaca.spcms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.xvaca.spcms.bean.ChannelBean;
import com.xvaca.spcms.bean.PartnerBean;
import com.xvaca.spcms.bean.PipeBean;
import com.xvaca.spcms.db.DBConnector;

public class PipeDAO {

	private static final Logger logger = Logger.getLogger(PipeDAO.class);
	
	private static String INSERT_PIPE_SQL = "INSERT INTO pipes (channel_id, name, cmd, partner_id, sync_url, price, deduction, description, status, created_at, updated_at, need_mo) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static String UPDATE_PIPE_SQL = "UPDATE pipes set channel_id = ?, name = ?, cmd = ?, partner_id = ?, sync_url = ?, price = ?, deduction = ?, description = ?, status = ?, updated_at = ?, need_mo = ? " +
			" 									WHERE id = ?";
	
	private static String SELECT_PIPE_SQL = "SELECT * FROM pipes p " +
			"										left join channels c on p.channel_id = c.id" +
			"										left join partners pn on p.partner_id = pn.id  " +
			"										order by p.id";
	
//	private static String SELECT_PIPE_BY_STATUS_SQL = "SELECT * FROM pipes p " +
//	"										left join channels c on p.channel_id = c.id" +
//	"										left join partners pn on p.partner_id = pn.id where p.status = '1' AND c.status = '1'  " +
//	"										order by p.id";
	
	private static String SELECT_PIPE_BY_ID_SQL = "SELECT * FROM pipes p" +
			"										left join channels c on p.channel_id = c.id" +
			"										left join partners pn on p.partner_id = pn.id  " +
			"										where p.id = ?";
	
	private static String SELECT_PIPE_BY_CHANNELID_AND_CMD_SQL = "SELECT * FROM pipes p" +
	"										left join channels c on p.channel_id = c.id" +
	"										left join partners pn on p.partner_id = pn.id  " +
	"										where p.status = '1' and p.channel_id = ? and p.cmd = LEFT(?, LENGTH(p.cmd))";
	
	/**
	 * 插入渠道信息
	 * @param pipe
	 */
	public PipeBean insertPipe(PipeBean pipe) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(INSERT_PIPE_SQL);
			pstmt.setInt(1, pipe.getChannelId());
			pstmt.setString(2, pipe.getName());
			pstmt.setString(3, pipe.getCmd().toUpperCase());
			pstmt.setInt(4, pipe.getPartnerId());
			pstmt.setString(5, pipe.getSyncUrl());
			pstmt.setInt(6, pipe.getPrice());
			pstmt.setDouble(7, pipe.getDeduction());
			pstmt.setString(8, pipe.getDescription());
			pstmt.setInt(9, pipe.getStatus());
			pstmt.setString(10, pipe.getCreatedAt());
			pstmt.setString(11, pipe.getUpdatedAt());
			pstmt.setInt(12, pipe.getNeedMO());
			
			pstmt.execute();
			
			pstmt = conn.prepareStatement("select LAST_INSERT_ID() as id");
			rs = pstmt.executeQuery();
			if (rs.next()) {
				pipe.setId(rs.getInt("id"));
			}
			
		} catch (Exception e) {
			logger.error("插入渠道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return pipe;
	}
	
	
	/**
	 * 更新渠道
	 * @param pipe
	 */
	public void updatePipe(PipeBean pipe) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(UPDATE_PIPE_SQL);
			pstmt.setInt(1, pipe.getChannelId());
			pstmt.setString(2, pipe.getName());
			pstmt.setString(3, pipe.getCmd().toUpperCase());
			pstmt.setInt(4, pipe.getPartnerId());
			pstmt.setString(5, pipe.getSyncUrl());
			pstmt.setInt(6, pipe.getPrice());
			pstmt.setDouble(7, pipe.getDeduction());
			pstmt.setString(8, pipe.getDescription());
			pstmt.setInt(9, pipe.getStatus());
			pstmt.setString(10, pipe.getUpdatedAt());
			pstmt.setInt(11, pipe.getNeedMO());
			pstmt.setInt(12, pipe.getId());
			
			pstmt.execute();
			
		} catch (Exception e) {
			logger.error("更新渠道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
	}
	
	/**
	 * 获取渠道列表
	 * @return
	 */
	public List<PipeBean> getPipeList() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<PipeBean> list = new ArrayList<PipeBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PIPE_SQL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				PipeBean pipe = new PipeBean();
				pipe.setChannelId(rs.getInt("p.channel_id"));
				pipe.setCmd(rs.getString("p.cmd"));
				pipe.setCreatedAt(rs.getString("p.created_at"));
				pipe.setDeduction(rs.getDouble("p.deduction"));
				pipe.setDescription(rs.getString("p.description"));
				pipe.setId(rs.getInt("p.id"));
				pipe.setName(rs.getString("p.name"));
				pipe.setPartnerId(rs.getInt("p.partner_id"));
				pipe.setPrice(rs.getInt("p.price"));
				pipe.setStatus(rs.getInt("p.status"));
				pipe.setSyncUrl(rs.getString("p.sync_url"));
				pipe.setUpdatedAt(rs.getString("p.updated_at"));
				pipe.setNeedMO(rs.getInt("p.need_mo"));
				
				ChannelBean channel = new ChannelBean();
				channel.setId(rs.getInt("c.id"));
				channel.setName(rs.getString("c.name"));
				channel.setCode(rs.getString("c.code"));
				
				PartnerBean partner = new PartnerBean();
				partner.setId(rs.getInt("pn.id"));
				partner.setName(rs.getString("pn.name"));
				
				pipe.setChannel(channel);
				pipe.setPartner(partner);
				
				list.add(pipe);
			}
		} catch (Exception e) {
			logger.error("查询渠道列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
	}
	
	/**
	 * 按状态获取渠道列表
	 * @return
	 */
	public Map<Integer, PipeBean> getPipeListByStatus() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<Integer, PipeBean> map = new HashMap<Integer, PipeBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PIPE_SQL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				PipeBean pipe = new PipeBean();
				pipe.setChannelId(rs.getInt("p.channel_id"));
				pipe.setCmd(rs.getString("p.cmd"));
				pipe.setCreatedAt(rs.getString("p.created_at"));
				pipe.setDeduction(rs.getDouble("p.deduction"));
				pipe.setDescription(rs.getString("p.description"));
				pipe.setId(rs.getInt("p.id"));
				pipe.setName(rs.getString("p.name"));
				pipe.setPartnerId(rs.getInt("p.partner_id"));
				pipe.setPrice(rs.getInt("p.price"));
				pipe.setStatus(rs.getInt("p.status"));
				pipe.setSyncUrl(rs.getString("p.sync_url"));
				pipe.setUpdatedAt(rs.getString("p.updated_at"));
				pipe.setNeedMO(rs.getInt("p.need_mo"));
				
				ChannelBean channel = new ChannelBean();
				channel.setId(rs.getInt("c.id"));
				channel.setName(rs.getString("c.name"));
				
				PartnerBean partner = new PartnerBean();
				partner.setId(rs.getInt("pn.id"));
				partner.setName(rs.getString("pn.name"));
				
				pipe.setChannel(channel);
				pipe.setPartner(partner);
				
				map.put(pipe.getId(), pipe);
			}
		} catch (Exception e) {
			logger.error("查询渠道列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return map;
	}
	
	/**
	 * 根据ID获取制定渠道
	 * @param id
	 * @return
	 */
	public PipeBean getPipeById(int id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PipeBean pipe = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PIPE_BY_ID_SQL);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				pipe = new PipeBean();
				pipe.setChannelId(rs.getInt("channel_id"));
				pipe.setCmd(rs.getString("cmd"));
				pipe.setCreatedAt(rs.getString("created_at"));
				pipe.setDeduction(rs.getDouble("deduction"));
				pipe.setDescription(rs.getString("description"));
				pipe.setId(rs.getInt("id"));
				pipe.setName(rs.getString("name"));
				pipe.setPartnerId(rs.getInt("partner_id"));
				pipe.setPrice(rs.getInt("price"));
				pipe.setStatus(rs.getInt("status"));
				pipe.setSyncUrl(rs.getString("sync_url"));
				pipe.setUpdatedAt(rs.getString("updated_at"));
				pipe.setNeedMO(rs.getInt("need_mo"));
				
				ChannelBean channel = new ChannelBean();
				channel.setId(rs.getInt("c.id"));
				channel.setName(rs.getString("c.name"));
				
				PartnerBean partner = new PartnerBean();
				partner.setId(rs.getInt("pn.id"));
				partner.setName(rs.getString("pn.name"));
				
				pipe.setChannel(channel);
				pipe.setPartner(partner);
				
			}
		} catch (Exception e) {
			logger.error("查询渠道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return pipe;
	}
	
	public PipeBean getPipeByChannelIdAndCmd(int channelId, String cmd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		PipeBean pipe = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_PIPE_BY_CHANNELID_AND_CMD_SQL);
			pstmt.setInt(1, channelId);
			pstmt.setString(2, cmd.toUpperCase());
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				pipe = new PipeBean();
				pipe.setChannelId(rs.getInt("channel_id"));
				pipe.setCmd(rs.getString("cmd"));
				pipe.setCreatedAt(rs.getString("created_at"));
				pipe.setDeduction(rs.getDouble("deduction"));
				pipe.setDescription(rs.getString("description"));
				pipe.setId(rs.getInt("id"));
				pipe.setName(rs.getString("name"));
				pipe.setPartnerId(rs.getInt("partner_id"));
				pipe.setPrice(rs.getInt("price"));
				pipe.setStatus(rs.getInt("status"));
				pipe.setSyncUrl(rs.getString("sync_url"));
				pipe.setUpdatedAt(rs.getString("updated_at"));
				pipe.setNeedMO(rs.getInt("need_mo"));
				
				ChannelBean channel = new ChannelBean();
				channel.setId(rs.getInt("c.id"));
				channel.setName(rs.getString("c.name"));
				channel.setCode(rs.getString("c.code"));
				
				PartnerBean partner = new PartnerBean();
				partner.setId(rs.getInt("pn.id"));
				partner.setName(rs.getString("pn.name"));
				
				pipe.setChannel(channel);
				pipe.setPartner(partner);
				
			}
		} catch (Exception e) {
			logger.error("查询渠道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return pipe;
	}
	
}
